package com.cs.paoyer.administration.business.entity;

//import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
//import java.util.HashMap;
//import java.util.Vector;
//import java.sql.PreparedStatement;
//import java.util.Iterator;

import com.cs.paoyer.administration.business.model.WarehouseByBranchVO;
//import com.cs.paoyer.common.util.Encrypter;
//import java.security.NoSuchAlgorithmException;
//
//import com.cs.paoyer.common.exception.UserException;
//import com.cs.paoyer.common.exception.SystemException;

/**
 * DAO para consultar registros en la vista VFD_BODEGAS_X_SURCURSAL
 * 
 * @author Rene.Rangel@consultsoft.com
 * @version 1.0
 */

public class WarehouseByBranchDAO
{
  /**
   * Conexion a la base de datos.
   */
  private Connection conn = null;

  /**
   * Constructor que inicializa la conexion con la base de datos.
   * @param conn Conexion con la base de datos.
   */
  
  public WarehouseByBranchDAO(Connection conn){
    this.conn = conn;
  }

/**
  * Consulta un grupo de registros de la vista VFD_BODEGAS_X_SUCURSAL a partir
  * de las condiciones que vienen en el Value Object
  *
  * @param WarehouseByBranchVO    Value Object con las condiciones a tener encuenta.
  * 
  * @return Collection            Arreglo de registros retornados de la consulta.
  */  
    public Collection findWarehouseBranchByCriterea(WarehouseByBranchVO criterea) throws SQLException 
    {
    
    /**
     * Se consulta sobre la vista creada para este fin
     */
    
    
    String query = "SELECT bosu.* " +
    "FROM vfd_bodegas_x_sucursal bosu ";

    String filters = "";
    // Filtro por Nombre de Sucursal
    if ( (criterea.getBranchName() != null) && !(criterea.getBranchName().equals("")) ) {
      if ( filters.equalsIgnoreCase("") ) {
        filters = filters + " UPPER(bosu.sucu_nombre) LIKE '%" + criterea.getBranchName().toUpperCase() + "%' ";
      }
      else {
        filters = filters + " AND UPPER(bosu.sucu_nombre) LIKE '%" + criterea.getBranchName().toUpperCase() + "%' ";
      }
    }

    // Filtro por Codigo Usuario de Sucursal
    if ( (criterea.getBranchCustomCode() != null) && !(criterea.getBranchCustomCode().intValue() == 0) ) {
      if ( filters.equalsIgnoreCase("") ) {
        filters = filters + " bosu.sucu_codusu = " + criterea.getBranchCustomCode();
      }
      else {
        filters = filters + " AND bosu.sucu_codusu = " + criterea.getBranchCustomCode();
      }
    }

    

    // Filtro por Codigo interno de Sucursal
    if ( (criterea.getBranchInternalCode() != null) && !(criterea.getBranchInternalCode().intValue() == 0) ) {
      if ( filters.equals("") ) {
        filters = filters + " bosu.sucu_codin = " + criterea.getBranchInternalCode();
      }
      else {
        filters = filters + " AND bosu.sucu_codin = " + criterea.getBranchInternalCode();
      }
    }

    
    // Filtro por Nombre de Bodega
    if ( (criterea.getWarehouseName() != null) && !(criterea.getWarehouseName().equals("")) ) {
      if ( filters.equals("") ) {
        filters = filters + " bosu.bode_nombre LIKE '%" + criterea.getWarehouseName().toUpperCase()+"%'";
      }
      else {
        filters = filters + " AND bosu.bode_nombre LIKE '%" + criterea.getWarehouseName().toUpperCase()+"%'";
      }
    }

    // Filtro por Bodega Activa
    if ( (criterea.getActiveWarehouse() != null) && !(criterea.getActiveWarehouse().equals("")) ) {
      if ( filters.equals("") ) {
        filters = filters + " bosu.bode_activo = '" + criterea.getActiveWarehouse()+"'";
      }
      else {
        filters = filters + " AND bosu.bode_activo = '" + criterea.getActiveWarehouse()+"'";
      }
    }

    // Filtro por Codigo Interno de Bodega
    if ( (criterea.getWarehouseInternalCode() != null) && !(criterea.getWarehouseInternalCode().intValue() == 0) ) {
      if ( filters.equals("") ) {
        filters = filters + " bosu.bode_codin = " + criterea.getWarehouseInternalCode();
      }
      else {
        filters = filters + " AND bosu.bode_codin = " + criterea.getWarehouseInternalCode();
      }
    }

    // Filtro por Codigo Interno de Clase de Bodega
    if ( (criterea.getWarehouseClassCode() != null) && !(criterea.getWarehouseClassCode().intValue() == 0) ) {
      if ( filters.equals("") ) {
        filters = filters + " bosu.clbo_codigo = " + criterea.getWarehouseClassCode();
      }
      else {
        filters = filters + " AND bosu.clbo_codigo = " + criterea.getWarehouseClassCode();
      }
    }

    // Filtro por Descripcion de Clase de Bodega
    if ( (criterea.getWarehouseClassDescription() != null) && !(criterea.getWarehouseClassDescription().equals("")) ) {
      if ( filters.equals("") ) {
        filters = filters + " bosu.clbo_descripcion = '" + criterea.getWarehouseClassDescription()+"'";
      }
      else {
        filters = filters + " AND bosu.clbo_descripcion = '" + criterea.getWarehouseClassDescription()+"'";
      }
    }

    // Filtro por Clase de Bodega Activa
    if ( (criterea.getActiveWarehouseClass() != null) && !(criterea.getActiveWarehouseClass().equals("")) ) {
      if ( filters.equals("") ) {
        filters = filters + " bosu.clbo_activo = '" + criterea.getActiveWarehouseClass()+"'";
      }
      else {
        filters = filters + " AND bosu.clbo_activo = '" + criterea.getActiveWarehouseClass()+"'";
      }
    }

    // Filtro por Sigla de Clase de Bodega 
    if ( (criterea.getWarehouseClassAbbreviation() != null) && !(criterea.getWarehouseClassAbbreviation().equals("")) ) {
      if ( filters.equals("") ) {
        filters = filters + " bosu.clbo_sigla = '" + criterea.getWarehouseClassAbbreviation()+"'";
      }
      else {
        filters = filters + " AND bosu.clbo_sigla = '" + criterea.getWarehouseClassAbbreviation()+"'";
      }
    }

    // Si hay filtros, entonces armar la clausula WHERE 
    if ( !filters.equals("") ) {
      query = query + " WHERE " + filters;
    }

    Statement stm = null;
    ResultSet rst = null;
    ArrayList warehouseBranchs = new ArrayList();
   
    stm = conn.createStatement();
    rst = stm.executeQuery(query);
    WarehouseByBranchVO warehouseBranch = null;
    while ( rst.next() ) 
    {
        warehouseBranch = new WarehouseByBranchVO();
        warehouseBranch.setBranchInternalCode(new Long(rst.getLong( "sucu_codin")));      
        warehouseBranch.setBranchCustomCode(new Short(rst.getShort( "sucu_codusu")));      
        warehouseBranch.setBranchName(rst.getString( "sucu_nombre"));      
        warehouseBranch.setBranchAddress(rst.getString( "sucu_direccion"));      
        warehouseBranch.setBranchPhoneNumber(rst.getString( "sucu_telefono"));      
        warehouseBranch.setActiveBranch(rst.getString( "sucu_activo"));      
        warehouseBranch.setBranchFax(rst.getString( "sucu_fax"));      
        warehouseBranch.setBranchPostOfficeBox(rst.getString( "sucu_apartado_aereo"));      
        warehouseBranch.setBranchCityCode(new Long(rst.getLong( "sucu_ciu_codigo")));      
        warehouseBranch.setBranchManager(rst.getString( "sucu_gerente"));      
        warehouseBranch.setWarehouseInternalCode(new Long(rst.getLong( "bode_codin")));      
        warehouseBranch.setWarehouseName(rst.getString( "bode_nombre"));      
        warehouseBranch.setActiveWarehouse(rst.getString( "bode_activo"));      
        warehouseBranch.setWarehouseClassCode(new Long(rst.getLong( "clbo_codigo")));      
        warehouseBranch.setWarehouseClassDescription(rst.getString( "clbo_descripcion"));      
        warehouseBranch.setActiveWarehouseClass(rst.getString( "clbo_activo"));      
        warehouseBranch.setWarehouseClassAbbreviation(rst.getString( "clbo_sigla"));      
        warehouseBranchs.add(warehouseBranch);
   }
         
    
   stm.close();
       
   return warehouseBranchs;
  }
  
  
  

}